In [1]:
pip install plotly

Requirement already satisfied: plotly in c:\users\devik\anaconda3\lib\site-packages (5.7.0)Note: you may need to restart the kernel to use updated packages.

[notice] A new release of pip available: 22.2.2 -> 22.3.1
[notice] To update, run: python.exe -m pip install --upgrade pip

Requirement already satisfied: tenacity>=6.2.0 in c:\users\devik\anaconda3\lib\site-packages (from plotly) (8.0.1)
Requirement already satisfied: six in c:\users\devik\anaconda3\lib\site-packages (from plotly) (1.16.0)
In [2]:
import pandas as pd 
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns 
import plotly.express as px
import plotly.graph_objects as go
In [3]:
data = pd.read_csv('carehome.csv')
In [4]:
data.head()
Out[4]:
Ref_ID Company ID COUNTRY REGION COUNTY DISTRICT LOCALAUTHORITY FULLNAME MAILINGNAME ADDRESS1 ... PROPERTYPURPOSEBUILT FIRSTYEARASCAREHOME YEAROFLASTMAJORCONVERSION INDUSTRIALWASHINGMACHINEON_SITE AGEFROM AGETO NOOFBEDSPRIVATELYFUNDED OPEN_STATUS LATITUDE LONGITUDE
0 2 NaN England Yorkshire & The Humber South Yorkshire Sheffield Area Sheffield City Council Aaron House Aaron House 20 Collegiate Crescent ... N 1992.0 2005.0 NaN 50.0 NaN NaN Open / Registered 53.3758 -1.48839
1 7 NaN England Yorkshire & The Humber South Yorkshire Sheffield Area Sheffield City Council Cairn Home Cairn Home 58 Selborne Road ... Y NaN 2001.0 Y NaN NaN NaN Open / Registered 53.3788 -1.51881
2 9 NaN England Yorkshire & The Humber South Yorkshire Sheffield Area Sheffield City Council White Rose Court White Rose Court 40/42 Clifton Avenue ... NaN 1997.0 NaN NaN NaN NaN NaN Open / Registered 53.3785 -1.39861
3 10 NaN England Yorkshire & The Humber South Yorkshire Sheffield Area Sheffield City Council Darwin House Darwin House Darwin Lane ... N 1992.0 NaN NaN 65.0 NaN NaN Open / Registered 53.3754 -1.52208
4 14 NaN England Yorkshire & The Humber South Yorkshire Sheffield Area Sheffield City Council Hallamshire Hallamshire Residential Home 3 Broomhall Road ... N 0.0 2013.0 NaN 60.0 NaN NaN Open / Registered 53.3746 -1.48619

5 rows × 116 columns

In [6]:
values = data['COUNTRY'].value_counts()
labels = data['COUNTRY'].unique().tolist()
fig = go.Figure(data= [go.Pie(values=values, labels = labels,pull = [0.2,0, 0, 0, 0, 0],title = 'Countrywisw Split of Care data')])

fig.show()
print(values)

England             15207
Scotland             1077
Wales                1075
Northern Ireland      439
Isle of Man            50
Channel Islands        50
Name: COUNTRY, dtype: int64
In [6]:
x = data.loc[data['COUNTRY'] == 'England']
# x
In [7]:
values = x['REGION'].value_counts()
labels = x['REGION'].unique().tolist()
fig = go.Figure(data= [go.Pie(values=values, labels = labels,pull = [0,0, 0, 0, 0, 0,0,0,0.2],title = 'Regionwise Split of England Care data')])

fig.show()
print(values)

South East England        2893
South West England        1994
North West England        1894
East of England           1670
West Midlands             1660
East Midlands             1521
Yorkshire & The Humber    1493
London                    1350
North East England         732
Name: REGION, dtype: int64
In [8]:
x = data.loc[data['REGION'] == 'London']
# x
In [9]:
values = x['DISTRICT'].value_counts()
labels = x['DISTRICT'].unique().tolist()
fig = go.Figure(data= [go.Pie(values=values, labels = labels,title = 'Districtwise Split of Care data')])

fig.show()
print(values)

Croydon Borough                 127
Barnet Borough                   83
Redbridge Borough                82
Enfield Borough                  81
Sutton Borough                   78
Brent Borough                    58
Havering Borough                 57
Lewisham Borough                 56
Harrow Borough                   55
Bromley Borough                  53
Waltham Forest Borough           49
Hillingdon Borough               47
Ealing Borough                   46
Richmond Borough                 44
Lambeth Borough                  44
Greenwich Borough                43
Kingston upon Thames Borough     38
Merton Borough                   37
Haringey Borough                 32
Wandsworth Borough               30
Bexley Borough                   30
Hounslow Borough                 30
Newham Borough                   27
Barking & Dagenham Borough       22
Southwark Borough                19
Islington Borough                16
Hackney Borough                  15
Kensington & Chelsea Borough     11
Westminster Borough              11
Camden Borough                   10
Tower Hamlets Borough            10
Hammersmith & Fulham Borough      9
Name: DISTRICT, dtype: int64
In [12]:
fig = px.density_mapbox(x,lat ='LATITUDE',lon='LONGITUDE',radius=2,center=dict(lat=51.500153 ,lon=-0.1262362),zoom=5,mapbox_style='stamen-terrain')
fig.show()
In [13]:
 data.describe()
Out[13]:
Ref_ID Company ID REGISTEREDMAXSERVICEUSERS GROUP_REFKEY SINGLEROOMS SHAREDROOMS ROOMSWITHENSUITEWC RESIDENTIALCAREFEEMIN RESIDENTIALCAREFEEMAX RESIDENTIALDEMENTIACAREFEEMIN ... NURSINGDEMENTIACAREFEEMIN NURSINGDEMENTIACAREFEEMAX FIRSTYEARASCAREHOME YEAROFLASTMAJORCONVERSION AGEFROM AGETO NOOFBEDSPRIVATELYFUNDED LATITUDE LONGITUDE No_carers_req
count 1.789800e+04 0.0 17898.000000 1.271400e+04 17885.000000 5698.000000 14616.000000 3726.000000 2972.000000 757.000000 ... 257.000000 151.000000 14684.000000 7681.000000 11394.000000 1197.000000 300.000000 17898.000000 17898.000000 17898.000000
mean 7.860081e+09 NaN 30.240083 7.915313e+09 28.668214 2.484029 25.350096 711.210682 848.794078 986.071334 ... 1202.428016 1337.821192 1408.518592 1961.660070 47.717307 80.252297 12.443333 52.605211 -1.677896 50.139792
std 2.127311e+10 NaN 24.791352 2.133770e+10 24.427943 2.737252 25.443301 348.914234 331.615874 570.698122 ... 280.592711 306.601393 908.839466 309.156311 20.247014 25.609882 13.910853 1.514636 1.640078 47.619370
min 2.000000e+00 NaN 1.000000 1.793000e+04 1.000000 0.000000 0.000000 300.000000 305.000000 100.000000 ... 650.000000 720.000000 0.000000 0.000000 1.000000 0.000000 0.000000 49.179540 -7.820670 0.000000
25% 1.235125e+04 NaN 9.000000 2.896500e+04 8.000000 1.000000 6.000000 515.500000 625.000000 770.000000 ... 1000.000000 1165.000000 0.000000 2006.000000 18.000000 65.000000 2.000000 51.437175 -2.704389 12.000000
50% 2.817550e+04 NaN 25.000000 5.406300e+04 22.000000 2.000000 16.000000 654.000000 800.000000 930.000000 ... 1200.000000 1300.000000 1989.000000 2011.000000 60.000000 65.000000 10.000000 52.410500 -1.551585 35.000000
75% 5.456975e+04 NaN 43.000000 6.637475e+04 41.000000 3.000000 40.000000 850.000000 1000.000000 1100.000000 ... 1395.000000 1500.000000 2003.000000 2016.000000 65.000000 100.000000 18.000000 53.534700 -0.385615 73.000000
max 6.543224e+10 NaN 225.000000 6.543224e+10 225.000000 40.000000 215.000000 14500.000000 4750.000000 14500.000000 ... 2100.000000 2200.000000 2022.000000 2021.000000 83.000000 200.000000 86.000000 60.757300 1.754220 430.000000

8 rows × 23 columns

In [14]:
no_carers = data.loc[data['REGION'] == 'London']
# no_carers
In [15]:
no_carers.describe()
Out[15]:
Ref_ID Company ID REGISTEREDMAXSERVICEUSERS GROUP_REFKEY SINGLEROOMS SHAREDROOMS ROOMSWITHENSUITEWC RESIDENTIALCAREFEEMIN RESIDENTIALCAREFEEMAX RESIDENTIALDEMENTIACAREFEEMIN ... NURSINGDEMENTIACAREFEEMIN NURSINGDEMENTIACAREFEEMAX FIRSTYEARASCAREHOME YEAROFLASTMAJORCONVERSION AGEFROM AGETO NOOFBEDSPRIVATELYFUNDED LATITUDE LONGITUDE No_carers_req
count 1.350000e+03 0.0 1350.000000 9.610000e+02 1347.000000 270.000000 1013.000000 194.000000 152.000000 48.000000 ... 21.000000 14.000000 1011.000000 454.000000 740.000000 110.000000 25.000000 1350.000000 1350.000000 1350.000000
mean 7.464152e+09 NaN 25.922222 6.740716e+09 24.972532 2.525926 26.080948 838.582474 1000.657895 1100.916667 ... 1333.285714 1560.357143 1472.727003 1952.687225 45.101351 74.227273 10.200000 51.499805 -0.120175 44.992593
std 2.080868e+10 NaN 28.070689 1.990056e+10 27.581030 3.857749 30.445463 313.326587 399.297547 286.968293 ... 339.289278 338.335657 878.358094 335.700308 20.717685 17.591000 17.682383 0.092562 0.161795 55.753202
min 4.434000e+03 NaN 1.000000 1.793300e+04 1.000000 0.000000 0.000000 300.000000 450.000000 450.000000 ... 650.000000 1050.000000 0.000000 0.000000 16.000000 40.000000 0.000000 51.296000 -0.495845 1.000000
25% 1.822125e+04 NaN 6.000000 2.802500e+04 6.000000 0.000000 4.000000 601.000000 700.000000 900.000000 ... 1200.000000 1350.000000 0.000000 2006.000000 18.000000 65.000000 1.000000 51.420850 -0.232187 7.000000
50% 3.827800e+04 NaN 12.000000 5.351000e+04 11.000000 2.000000 11.000000 800.000000 901.500000 1100.000000 ... 1219.000000 1557.500000 1993.000000 2011.000000 55.000000 65.000000 4.000000 51.513350 -0.120602 17.000000
75% 5.577850e+04 NaN 40.000000 5.833500e+04 37.000000 3.000000 44.000000 1000.000000 1221.500000 1262.500000 ... 1500.000000 1650.000000 2005.500000 2015.000000 65.000000 80.000000 11.000000 51.576475 -0.000349 66.000000
max 6.543224e+10 NaN 215.000000 6.543224e+10 215.000000 40.000000 215.000000 1995.000000 3000.000000 1750.000000 ... 2100.000000 2200.000000 2022.000000 2021.000000 80.000000 118.000000 86.000000 51.680600 0.298913 430.000000

8 rows × 23 columns

In [16]:
SEE = data.loc[data['REGION'] == 'South East England']
# SEE
In [17]:
SEE_plot = x.iloc[0:2893,5:7].value_counts()
# print(SEE_plot)
SEE_plot.plot.bar(title="South East England",width = 0.9,color=('grey','skyblue'))
plt.show()
In [18]:
def sum_frame_by_column(frame, new_col_name, list_of_cols_to_sum):
    frame[new_col_name] = frame[list_of_cols_to_sum].astype(float).sum(axis=1)
    return(frame)
In [19]:
sum_frame_by_column(data, 'No_carers_req', ['SINGLEROOMS','SHAREDROOMS','ROOMSWITHENSUITEWC'])
Out[19]:
Ref_ID Company ID COUNTRY REGION COUNTY DISTRICT LOCALAUTHORITY FULLNAME MAILINGNAME ADDRESS1 ... FIRSTYEARASCAREHOME YEAROFLASTMAJORCONVERSION INDUSTRIALWASHINGMACHINEON_SITE AGEFROM AGETO NOOFBEDSPRIVATELYFUNDED OPEN_STATUS LATITUDE LONGITUDE No_carers_req
0 2 NaN England Yorkshire & The Humber South Yorkshire Sheffield Area Sheffield City Council Aaron House Aaron House 20 Collegiate Crescent ... 1992.0 2005.0 NaN 50.0 NaN NaN Open / Registered 53.375800 -1.488390 32.0
1 7 NaN England Yorkshire & The Humber South Yorkshire Sheffield Area Sheffield City Council Cairn Home Cairn Home 58 Selborne Road ... NaN 2001.0 Y NaN NaN NaN Open / Registered 53.378800 -1.518810 60.0
2 9 NaN England Yorkshire & The Humber South Yorkshire Sheffield Area Sheffield City Council White Rose Court White Rose Court 40/42 Clifton Avenue ... 1997.0 NaN NaN NaN NaN NaN Open / Registered 53.378500 -1.398610 29.0
3 10 NaN England Yorkshire & The Humber South Yorkshire Sheffield Area Sheffield City Council Darwin House Darwin House Darwin Lane ... 1992.0 NaN NaN 65.0 NaN NaN Open / Registered 53.375400 -1.522080 39.0
4 14 NaN England Yorkshire & The Humber South Yorkshire Sheffield Area Sheffield City Council Hallamshire Hallamshire Residential Home 3 Broomhall Road ... 0.0 2013.0 NaN 60.0 NaN NaN Open / Registered 53.374600 -1.486190 50.0
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
17893 65432244740 NaN England Yorkshire & The Humber North Lincolnshire North Lincolnshire Area North Lincolnshire Council Barton House Barton House Tofts Road ... 0.0 NaN NaN NaN NaN NaN Open / Registered 53.681847 -0.448382 8.0
17894 65432244744 NaN England South West England Cornwall Cornwall Area Cornwall Council Newport Square Newport Square 5 Newport Square ... 0.0 NaN NaN NaN NaN NaN Open / Registered 50.642185 -4.366169 2.0
17895 65432244835 NaN England Yorkshire & The Humber West Yorkshire Wakefield Area City of Wakefield Metropolitan District Council Bennett Court Bennett Court Ash Grove ... 0.0 NaN NaN 18.0 NaN NaN Open / Registered 53.599236 -1.286717 60.0
17896 65432244910 NaN England South East England Surrey Woking Area Surrey County Council Charrington Manor Care Home Charrington Manor Care Home 1A Hobbs Close ... 0.0 NaN NaN 65.0 NaN NaN Opening 01 Feb 2022 / Not Registered 51.337630 -0.499874 160.0
17897 65432244941 NaN England East of England Suffolk Suffolk Coastal Area Suffolk County Council Cavell Manor Care Home Cavell Manor Care Home Bredfield Road ... 2021.0 NaN NaN 65.0 NaN NaN Open / Registered 52.102142 1.314010 110.0

17898 rows × 117 columns

In [20]:
no_carers = data.loc[data['REGION'] == 'London']
In [21]:
no_carers
Out[21]:
Ref_ID Company ID COUNTRY REGION COUNTY DISTRICT LOCALAUTHORITY FULLNAME MAILINGNAME ADDRESS1 ... FIRSTYEARASCAREHOME YEAROFLASTMAJORCONVERSION INDUSTRIALWASHINGMACHINEON_SITE AGEFROM AGETO NOOFBEDSPRIVATELYFUNDED OPEN_STATUS LATITUDE LONGITUDE No_carers_req
1635 4434 NaN England London London Westminster Borough Westminster City Council Flat C, 291 Harrow Road Flat C, 291 Harrow Road NaN ... 1987.0 2002.0 Y NaN NaN NaN Open / Registered 51.523100 -0.196379 5.0
1685 4594 NaN England London London Lambeth Borough London Borough of Lambeth Council Drewstead Lodge Drewstead Lodge 93 Drewstead Road ... NaN NaN NaN NaN NaN NaN Open / Registered 51.438500 -0.136329 1.0
1688 4599 NaN England London London Lambeth Borough London Borough of Lambeth Council 31 Woodbourne Avenue 31 Woodbourne Avenue NaN ... 0.0 0.0 N 18.0 NaN NaN Open / Registered 51.433200 -0.130317 8.0
1689 4600 NaN England London London Lambeth Borough London Borough of Lambeth Council Joybrook Joybrook 86 Braxted Park ... NaN 2004.0 NaN NaN NaN NaN Open / Registered 51.418300 -0.122107 14.0
1704 4646 NaN England London London Lambeth Borough London Borough of Lambeth Council St Peter's Residence St Peter's Residence 2a Meadow Road ... 1986.0 NaN Y 65.0 NaN NaN Open / Registered 51.482700 -0.119665 112.0
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
17795 65432243126 NaN England London London Redbridge Borough London Borough of Redbridge Council Fari Care Ltd Fari Care Ltd 607 Green Lane ... 0.0 NaN NaN NaN NaN NaN Open / Registered 51.563316 0.113586 6.0
17821 65432243395 NaN England London London Croydon Borough London Borough of Croydon Council Barchester Peony Court Care Home Barchester Peony Court Care Home 58 Addiscombe Road ... 2021.0 NaN NaN 55.0 NaN NaN Open / Registered 51.374866 -0.080551 68.0
17843 65432243673 NaN England London London Lewisham Borough London Borough of Lewisham Council PACO Daneby PACO Daneby 104 Daneby Road ... 0.0 NaN NaN NaN NaN NaN Open / Registered 51.435253 -0.011607 4.0
17846 65432243765 NaN England London London Lambeth Borough London Borough of Lambeth Council Inaya Care Inaya Care 3-7 Sunnyhill Road ... 0.0 NaN NaN 60.0 NaN NaN Opening 01 Dec 2021 / Not Registered 51.430584 -0.127129 50.0
17864 65432244208 NaN England London London Redbridge Borough London Borough of Redbridge Council Wellspring Care Home Wellspring Care Home 6 Glencoe Avenue ... 0.0 NaN NaN NaN NaN NaN Opening 01 Jan 2022 / Not Registered 51.567825 0.093247 4.0

1350 rows × 117 columns

In [22]:
no_carers.describe()
Out[22]:
Ref_ID Company ID REGISTEREDMAXSERVICEUSERS GROUP_REFKEY SINGLEROOMS SHAREDROOMS ROOMSWITHENSUITEWC RESIDENTIALCAREFEEMIN RESIDENTIALCAREFEEMAX RESIDENTIALDEMENTIACAREFEEMIN ... NURSINGDEMENTIACAREFEEMIN NURSINGDEMENTIACAREFEEMAX FIRSTYEARASCAREHOME YEAROFLASTMAJORCONVERSION AGEFROM AGETO NOOFBEDSPRIVATELYFUNDED LATITUDE LONGITUDE No_carers_req
count 1.350000e+03 0.0 1350.000000 9.610000e+02 1347.000000 270.000000 1013.000000 194.000000 152.000000 48.000000 ... 21.000000 14.000000 1011.000000 454.000000 740.000000 110.000000 25.000000 1350.000000 1350.000000 1350.000000
mean 7.464152e+09 NaN 25.922222 6.740716e+09 24.972532 2.525926 26.080948 838.582474 1000.657895 1100.916667 ... 1333.285714 1560.357143 1472.727003 1952.687225 45.101351 74.227273 10.200000 51.499805 -0.120175 44.992593
std 2.080868e+10 NaN 28.070689 1.990056e+10 27.581030 3.857749 30.445463 313.326587 399.297547 286.968293 ... 339.289278 338.335657 878.358094 335.700308 20.717685 17.591000 17.682383 0.092562 0.161795 55.753202
min 4.434000e+03 NaN 1.000000 1.793300e+04 1.000000 0.000000 0.000000 300.000000 450.000000 450.000000 ... 650.000000 1050.000000 0.000000 0.000000 16.000000 40.000000 0.000000 51.296000 -0.495845 1.000000
25% 1.822125e+04 NaN 6.000000 2.802500e+04 6.000000 0.000000 4.000000 601.000000 700.000000 900.000000 ... 1200.000000 1350.000000 0.000000 2006.000000 18.000000 65.000000 1.000000 51.420850 -0.232187 7.000000
50% 3.827800e+04 NaN 12.000000 5.351000e+04 11.000000 2.000000 11.000000 800.000000 901.500000 1100.000000 ... 1219.000000 1557.500000 1993.000000 2011.000000 55.000000 65.000000 4.000000 51.513350 -0.120602 17.000000
75% 5.577850e+04 NaN 40.000000 5.833500e+04 37.000000 3.000000 44.000000 1000.000000 1221.500000 1262.500000 ... 1500.000000 1650.000000 2005.500000 2015.000000 65.000000 80.000000 11.000000 51.576475 -0.000349 66.000000
max 6.543224e+10 NaN 215.000000 6.543224e+10 215.000000 40.000000 215.000000 1995.000000 3000.000000 1750.000000 ... 2100.000000 2200.000000 2022.000000 2021.000000 80.000000 118.000000 86.000000 51.680600 0.298913 430.000000

8 rows × 23 columns